﻿using System.ComponentModel.DataAnnotations;
using System.Data.EntityClient;
using System.Linq;
using System.ServiceModel.DomainServices.Server;
using System.Web.Configuration;
using System.ServiceModel.DomainServices.Hosting;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Threading; 
namespace LSW.Wcf.App.WcfService
{
    public class wcfServiceData : DomainService
    {
        /// <summary>
        /// Override the Count method in order for paging to work correctly
        /// </summary>
        protected override int Count<T>(IQueryable<T> query)
        {
            return query.Count();
        }

        #region SumaryTimeSheet
        [Query(IsDefault=true)]
        public IEnumerable<SumaryTimeSheets> GetSumaryTimeSheetsDefault()
        {
            return new List<SumaryTimeSheets>(); 
        }

        [Query]
        public IEnumerable<SumaryTimeSheets> GetSumaryTimeSheets(DateTime? FromDate, DateTime? ToDate, string EmployeeID)
        {
            try
            {
                using (MSSQLFactory conn = new MSSQLFactory())
                {
                    conn.open(Utility.GetConnectString());

                    DbParameter[] paras = new DbParameter[]{ new DbParameter("EmployeeID",EmployeeID),
                                                                new DbParameter("FromDate",FromDate),
                                                                new DbParameter("ToDate",ToDate)};
                    paras.SetDBNull();  
                    DataSet  ds = conn.runDynamicSqlQuery("SELECT * FROM dbo.GetSumaryTimeSheets(@EmployeeID,@FromDate,@ToDate)", paras, System.Data.CommandType.Text);

                    Utility.log.Info("Conplete Get Data...");  

                    return ds.Tables[0].ToList<SumaryTimeSheets>();
                }
            }
            catch (Exception exp)
            {
                Utility.log.Error(exp.InnerException.Message, exp);  
            }
            return null;
        }
        #endregion

        #region Excute Store
        [Query(IsDefault = true)]
        public IEnumerable<ReturnResult> ExcuteStoreDefault()
        {
            return new List<ReturnResult>();
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore1(string NameStore, string Para1)
        {
            return ExcuteStore2(NameStore, Para1, null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore2(string NameStore, string Para1, string Para2)
        {
            return ExcuteStore3(NameStore, Para1, Para2, null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore3(string NameStore, string Para1, string Para2, string Para3)
        {
            return ExcuteStore4(NameStore, Para1, Para2, Para3, null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore4(string NameStore, string Para1, string Para2, string Para3, string Para4)
        {
            return ExcuteStore5(NameStore, Para1, Para2, Para3, Para4,  null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore5(string NameStore, string Para1, string Para2, string Para3, string Para4, string Para5)
        {
            return ExcuteStore6(NameStore, Para1, Para2, Para3, Para4, Para5, null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore6(string NameStore, string Para1, string Para2, string Para3, string Para4, string Para5, string Para6)
        {
            return ExcuteStore7(NameStore, Para1, Para2, Para3, Para4, Para5, Para6, null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore7(string NameStore, string Para1, string Para2, string Para3, string Para4, string Para5, string Para6, string Para7)
        {
            return ExcuteStore8(NameStore, Para1, Para2, Para3, Para4, Para5, Para6, Para7,  null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore8(string NameStore, string Para1, string Para2, string Para3, string Para4, string Para5, string Para6, string Para7, string Para8)
        {
            return ExcuteStore9(NameStore, Para1, Para2, Para3, Para4, Para5, Para6, Para7, Para8,  null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore9(string NameStore, string Para1, string Para2, string Para3, string Para4, string Para5, string Para6, string Para7, string Para8, string Para9)
        {
            return ExcuteStore10(NameStore, Para1, Para2, Para3, Para4, Para5, Para6, Para7, Para8, Para9, null);
        }

        [Query]
        public IEnumerable<ReturnResult> ExcuteStore10(string NameStore, string Para1, string Para2, string Para3, string Para4, string Para5, string Para6, string Para7, string Para8, string Para9, string Para10)
        {
            List<ReturnResult> result =  new List<ReturnResult>();

            List<object> Paramaters = new List<object>();
            if(Para1 !=null) Paramaters.Add(Para1);
            if (Para2 != null) Paramaters.Add(Para2); 
            if (Para3 != null) Paramaters.Add(Para3);
            if (Para4 != null) Paramaters.Add(Para4);
            if (Para5 != null) Paramaters.Add(Para5);
            if (Para6 != null) Paramaters.Add(Para6);
            if (Para7 != null) Paramaters.Add(Para7);
            if (Para8 != null) Paramaters.Add(Para8);
            if (Para9 != null) Paramaters.Add(Para9);
            if (Para10 != null) Paramaters.Add(Para10);

            using (MSSQLFactory conn = new MSSQLFactory())
            {
                try
                {
                    conn.open(Utility.GetConnectString());

                    SqlCommand cm = new SqlCommand(NameStore, conn.Connection);

                    cm.CommandType = CommandType.StoredProcedure;

                    SqlCommandBuilder.DeriveParameters(cm);

                    // Populate the Input Parameters With Values Provided        
                    int j = 0;
                    for (int i = 1; i < cm.Parameters.Count; i++)
                    {
                        if (Paramaters.Count() > j)
                            if (Paramaters.ElementAt(j) == null)
                                cm.Parameters[i].Value = DBNull.Value;
                            else
                                if (cm.Parameters[i].DbType == DbType.Guid)
                                {
                                    Guid guid;
                                    if(!Guid.TryParse( Paramaters.ElementAt(j).ToString(), out guid))
                                        cm.Parameters[i].Value = DBNull.Value;
                                    else
                                        cm.Parameters[i].Value = Guid.Parse(Paramaters.ElementAt(j).ToString());
                                }
                                else
                                    cm.Parameters[i].Value = Paramaters.ElementAt(j);
                        j++;
                    }

                    cm.ExecuteNonQuery();
                    foreach (SqlParameter para in cm.Parameters.Cast<SqlParameter>().Where(o => o.Direction == ParameterDirection.InputOutput))
                    {
                        result.Add(new ReturnResult() { Result = para.Value.ToString() });     
                    }
                }
                catch (SqlException ex)
                {
                    Utility.log.Error(ex.InnerException.Message, ex);  
                }
            }
            return result;
        }
        #endregion

        #region Excute SQL
        [Query]
        public IEnumerable<ReturnResult> ExcuteSQL(string SQL)
        {
            List<ReturnResult> result = new List<ReturnResult>();
            using (MSSQLFactory conn = new MSSQLFactory())
            {
                try
                {
                    conn.open(Utility.GetConnectString());

                    SqlCommand cm = new SqlCommand(SQL, conn.Connection);
                    cm.CommandType = CommandType.Text;
                    cm.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    result.Add(new ReturnResult() { ErrorMessage = ex.Message });
                    Utility.log.Error(ex.InnerException.Message, ex);  
                }
            }

            return result;
        }
        #endregion

        #region View Object
        [Query(IsDefault=true)]
        public IEnumerable<WcfVObject> GetVObject()
        {
            try
            {
                using (MSSQLFactory conn = new MSSQLFactory())
                {
                    conn.open(Utility.GetConnectString());

                    DataSet ds = conn.runSQLQuery("SELECT * FROM vObject", System.Data.CommandType.Text);

                    Utility.log.Info("Conplete Get Data...");

                    return ds.Tables[0].ToList<WcfVObject>();
                }
            }
            catch (Exception exp)
            {
                Utility.log.Error(exp.InnerException.Message, exp);
            }
            return null;
        }

        [Query(IsDefault = true)]
        public IEnumerable<WcfVDebtPayment> GetVDebtPayment()
        {
            try
            {
                using (MSSQLFactory conn = new MSSQLFactory())
                {
                    conn.open(Utility.GetConnectString());

                    DataSet ds = conn.runSQLQuery("SELECT * FROM vDebtPayment", System.Data.CommandType.Text);

                    Utility.log.Info("Conplete Get Data...");

                    return ds.Tables[0].ToList<WcfVDebtPayment>();
                }
            }
            catch (Exception exp)
            {
                Utility.log.Error(exp.InnerException.Message, exp);
            }
            return null;
        }

        [Query(IsDefault = true)]
        public IEnumerable<WcfVDebtReciept> GetVDebtReciept()
        {
            try
            {
                using (MSSQLFactory conn = new MSSQLFactory())
                {
                    conn.open(Utility.GetConnectString());

                    DataSet ds = conn.runSQLQuery("SELECT * FROM vDebtReciept", System.Data.CommandType.Text);

                    Utility.log.Info("Conplete Get Data...");

                    return ds.Tables[0].ToList<WcfVDebtReciept>();
                }
            }
            catch (Exception exp)
            {
                Utility.log.Error(exp.InnerException.Message, exp);
            }
            return null;
        }

        #endregion

        #region AdvanceEmployees
        [Query(IsDefault = true)]
        public IEnumerable<WcfAdvanceEmployees> GetAdvanceEmployees()
        {
            return new List<WcfAdvanceEmployees>();
        }

        [Insert]
        public void InsertAdvanceEmployees(WcfAdvanceEmployees wcfAdvanceEmployees)
        {
            IDbTransaction trans = null;
            try
            {
                using (MSSQLFactory conn = new MSSQLFactory())
                {
                    conn.open(Utility.GetConnectString());

                    trans = conn.BeginTransaction();

                    string SQL = "INSERT INTO [PaymentList]([DocumentID],[DocumentDate],[ObjectID],[ReasonID],[Description]) " +
                                 "VALUES(@DocumentID, @DocumentDate, @ObjectID, @ReasonID, @Description) ";

                    DbParameter[] paraMater = new DbParameter[] {
                                                            new DbParameter("DocumentID",wcfAdvanceEmployees.DocumentID),
                                                            new DbParameter("DocumentDate",wcfAdvanceEmployees.DocumentDate),
                                                            new DbParameter("ObjectID",wcfAdvanceEmployees.EmployeeID),
                                                            new DbParameter("ReasonID",wcfAdvanceEmployees.ReasonID),
                                                            new DbParameter("Description",wcfAdvanceEmployees.Notes),
                                        };

                    paraMater.SetDBNull(); 
                    conn.runDynamicSqlNoQuery(SQL, paraMater, CommandType.Text, trans);

                    SQL = "INSERT INTO [PaymentListDetail]([DocumentID],[Amount],[Note]) " +
                          "VALUES(@DocumentID, @Amount, @Note) ";


                    DbParameter[] paraDetail = new DbParameter[] {
                                                            new DbParameter("DocumentID",wcfAdvanceEmployees.DocumentID),
                                                            new DbParameter("Amount",wcfAdvanceEmployees.Amount),
                                                            new DbParameter("Note",wcfAdvanceEmployees.Notes)
                                        };
                    paraDetail.SetDBNull();
                    conn.runDynamicSqlNoQuery(SQL, paraDetail, CommandType.Text, trans);

                    trans.Commit();
                    trans = null;

                }
            }
            catch (Exception exp)
            {
                Utility.log.Error(exp.InnerException.Message, exp);
            }
            finally 
            {
                if (trans != null)
                    trans.Rollback();
                trans = null;
            }
        }
        #endregion
    }
}